package com.boco.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.tree.RowMapper;

import com.boco.entity.RS_HZJBXX;

public class GetRS_HZJBXXData {
	//按照传来的表名去取得核心库的数据，并返回。

	public static List<RS_HZJBXX> getData()
	{
	    Connection con = null;// 创建一个数据库连接
	    PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
	    ResultSet result = null;// 创建一个结果集对象
	    List<RS_HZJBXX> re = new ArrayList<RS_HZJBXX>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
	        String user = "sjzx_hxk";// 用户名,系统默认的账户名
	        String password = "boco#1234";// 你安装时选设置的密码
	        con = DriverManager.getConnection(url, user, password);// 获取连接
	        System.out.println("连接成功！");
	        String sql = "select "
	        		+" '' as HZZSYH,"
	        		+" '' as QYZSY,"
	        		+" a.yljgdm as YLJGZZJGDM,"
	        		+" a.zjhm as SFZJHM,"
	        		+" a.zjlx as SFZJLBDM,"
	        		+" a.jkkh as JKKH,case when a.klx = '01' then a.kh else '' end YBKH,a.xm"
	        		 +" as HZXM,to_char(a.csrq,'yyyy-MM-dd HH24:mi:ss') as CSRQ,a.xb as XB,a.hyzk as HYZKDM,a.mz as MZ,c.zylbdm as  ZYLBDM,a.gzdwmc as GZDWMC,b.yyxzqh as XZQHDM,a.hkdz as HJQDZ,'' as HJDZSZZQZXS,'' as"
	        		+"  HJDZSDQZ,'' as HJDZXQ,'' as HJDZXZJDBSC,'' as HJDZCJLND,'' as HJDZMPHM,a.jzdz as XZQDZ,'' as XZDZSZZQZXS,'' as XZDZSDQZ,'' as XZDZXQ,'' as XZDZXZJDBSC,'' as XZDZCJLND,'' as XZDZMPHM,c.xzzyb as YZBM,a.dhhm as HZDHHM,a.gzdwdhhm as GZDWDHHM,a"
	        		+" .lxrdh as LXRDHHM,a.lxrxm as LXRXM,'' as YLBXLBDM,to_char(a.ywscsj,'yyyy-MM-dd HH24:mi:ss') as JDRQSJ,b.yymc as JDYY,'' as JDZGH,'' as JDZXM,'' as ISBABY,'' as MOTHER_ID,'' as MOTHER_NAME,'' as"
	        		+"  PARITY,'' as MULTI_BIRTH_IDEN,'' as BIRTH_ORDER,'' as SFSLJKK,'' as HEAD_URL,a."
	        		+" yljgdm as ORGCODE,'' as AREACODE,to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,'' as SOURCEID from tb_hzxx a"
	        		+" left join tb_yljgxxb b on a.yljgdm = b.yyid left join tb_ba_jbxx c on a.yljgdm ="
	        		+"  c.yljgdm and a.kh = c.kh";
	        
//	        System.out.println(sql);
// 预编译语句，“？”代表参数
	          
	        pre = con.prepareStatement(sql);// 实例化预编译语句
	        result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
	        System.out.println("执行成功");
	        while (result.next()){
	            // 当结果集不为空时
//	            System.out.println(result.getInt("YYDAH"));
	        	RS_HZJBXX temp = new RS_HZJBXX();
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setQYZSY(result.getString("QYZSY"));
	        	temp.setYLJGZZJGDM(result.getString("YLJGZZJGDM"));
	        	temp.setSFZJHM(result.getString("SFZJHM"));
	        	temp.setSFZJLBDM(result.getString("SFZJLBDM"));
	        	temp.setJKKH(result.getString("JKKH"));
	        	temp.setYBKH(result.getString("YBKH"));
	        	temp.setHZXM(result.getString("HZXM"));
	        	temp.setCSRQ(result.getString("CSRQ"));
	        	temp.setXB(result.getString("XB"));
	        	temp.setHYZKDM(result.getString("HYZKDM"));
	        	temp.setMZ(result.getString("MZ"));
	        	temp.setZYLBDM(result.getString("ZYLBDM"));
	        	temp.setGZDWMC(result.getString("GZDWMC"));
	        	temp.setXZQHDM(result.getString("XZQHDM"));
	        	temp.setHJQDZ(result.getString("HJQDZ"));
	        	temp.setHJDZSZZQZXS(result.getString("HJDZSZZQZXS"));
	        	temp.setHJDZSDQZ(result.getString("HJDZSDQZ"));
	        	temp.setHJDZXQ(result.getString("HJDZXQ"));
	        	temp.setHJDZXZJDBSC(result.getString("HJDZXZJDBSC"));
	        	temp.setHJDZCJLND(result.getString("HJDZCJLND"));
	        	temp.setHJDZMPHM(result.getString("HJDZMPHM"));
	        	temp.setXZQDZ(result.getString("XZQDZ"));
	        	temp.setXZDZSZZQZXS(result.getString("XZDZSZZQZXS"));
	        	temp.setXZDZSDQZ(result.getString("XZDZSDQZ"));
	        	temp.setXZDZXQ(result.getString("XZDZXQ"));
	        	temp.setXZDZXZJDBSC(result.getString("XZDZXZJDBSC"));
	        	temp.setXZDZCJLND(result.getString("XZDZCJLND"));
	        	temp.setXZDZMPHM(result.getString("XZDZMPHM"));
	        	temp.setYZBM(result.getString("YZBM"));
	        	temp.setHZDHHM(result.getString("HZDHHM"));
	        	temp.setGZDWDHHM(result.getString("GZDWDHHM"));
	        	temp.setLXRDHHM(result.getString("LXRDHHM"));
	        	temp.setLXRXM(result.getString("LXRXM"));
	        	temp.setYLBXLBDM(result.getString("YLBXLBDM"));
	        	temp.setJDRQSJ(result.getString("JDRQSJ"));
	        	temp.setJDYY(result.getString("JDYY"));
	        	temp.setJDZGH(result.getString("JDZGH"));
	        	temp.setJDZXM(result.getString("JDZXM"));
	        	temp.setISBABY(result.getString("ISBABY"));
	        	temp.setMOTHER_ID(result.getString("MOTHER_ID"));
	        	temp.setMOTHER_NAME(result.getString("MOTHER_NAME"));
	        	temp.setPARITY(result.getString("PARITY"));
	        	temp.setMULTI_BIRTH_IDEN(result.getString("MULTI_BIRTH_IDEN"));
	        	temp.setBIRTH_ORDER(result.getString("BIRTH_ORDER"));
	        	temp.setSFSLJKK(result.getString("SFSLJKK"));
	        	temp.setHEAD_URL(result.getString("HEAD_URL"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
	            // 注意关闭的顺序，最后使用的最先关闭
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
